package crm.control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import crm.dbcoon.DBConnect;
import crm.model.Employees;
import crm.util.Pagination;

public class EmployeesControl {
	
	public static Pagination pageInstance = null;
	/**
	 * 根据姓名获取employee
	 * @param name
	 * @return
	 */
	public static int getEmployeeIdByName(String employeeName) {
		DBConnect dbc=new DBConnect();
		String sql = "select id from info_employees where name like '"+employeeName+"'";
		dbc.prepareStatement(sql);
		try {
			
		
			ResultSet rs=dbc.executeQuery();
			while (rs.next()) {
			Employees employees = new Employees();
			employees.setId(rs.getInt("id"));
			
			int id = employees.getId();
			
			return id;
			}
			return 0;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return 0;
		}finally{
			dbc.close();
		}
		
	}
	
	public static Boolean employeesNameIsExist(String name) {
		DBConnect dbc=new DBConnect();
		String sql="select * from info_employees where name='"+name+"'";
		dbc.prepareStatement(sql);
		try {
			ResultSet rs=dbc.executeQuery();
			while (rs.next()) {
				return false;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
		return true;
	}
	

	@SuppressWarnings("unchecked")
	public static List getEmployeesList(){
		DBConnect dbc = new DBConnect();
		String sql = "select * from info_employees";
		List list = new ArrayList();
		dbc.prepareStatement(sql);
		ResultSet rs = null;
		try {
			rs = dbc.executeQuery();
			while(rs.next()){
				list.add(rs.getString("name"));
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
			
		} finally {
			dbc.close();
		}
	}
	public static String getEmployeesName(int id){
		DBConnect dbc = new DBConnect();
		String sql = "select name from info_employees where id = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1, id);
			ResultSet rs = dbc.executeQuery();
			if(rs.next()){
				System.out.println("AAAA"+rs.getString("name")+"DDD");
				return rs.getString("name");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally{
			dbc.close();
		}
		return null;
	}
public static int getUid(String username,String password){
		
		DBConnect dbc = new DBConnect();
		String sql = "SELECT id FROM info_employees WHERE username = ? AND password = ?";
		int uid=0;
		dbc.prepareStatement(sql);
		try {
			dbc.setString(1, username);
			dbc.setString(2, password);
			ResultSet rs = dbc.executeQuery();
			if(rs.next()){
				uid= rs.getInt("id");
				return uid;
			}
			else{
				return uid;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbc.close();
		}
		return uid;
	}

	public static int getEmployeesID(String username){
		DBConnect dbc = new DBConnect();
		String sql = "SELECT * FROM info_employees WHERE username = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setString(1,username);
			ResultSet rs = dbc.executeQuery();
			if(rs.next())
			return rs.getInt("id");
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			dbc.close();
		}
		return 0;
	}
	public static boolean isEmployees(String username){
		
		DBConnect dbc = new DBConnect();
		String sql = "SELECT * FROM info_employees WHERE username = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setString(1, username);
			ResultSet rs = dbc.executeQuery();
			if(rs.next()){
				return true;
			}
			else{
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbc.close();
		}
		return false;
	}
	public static boolean isEmployees(String username,String password){
		
		DBConnect dbc = new DBConnect();
		String sql = "SELECT * FROM info_employees WHERE username = ? AND password = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setString(1, username);
			dbc.setString(2, password);
			ResultSet rs = dbc.executeQuery();
			if(rs.next()){
				return true;
			}
			else{
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbc.close();
		}
		return false;
	}
	
	public static Employees getEmpolyeesById(int id){
		DBConnect dbc = new DBConnect();
		Employees employees = new Employees();
		String sql = "SELECT * FROM info_employees WHERE id = ?";
		dbc.prepareStatement(sql);
		ResultSet rs = null;
		try {
			dbc.setInt(1, id);
			rs =(ResultSet) dbc.executeQuery();
			if(rs.next())
			{
				getByRs(employees, rs);
				return employees;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
		return null;
	}
	

	
	public static boolean addEmployees(Employees employees){
		DBConnect dbc=new DBConnect();
		String sql = "insert into info_employees (idcard,name,username,password,gender,birthday,nation,polity,is_married,home_type,baby_birth,position,cellphone,phone,email,edu_level,family_address,area_id,start_time,namepy)" +
				" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		dbc.prepareStatement(sql);
		try
		{	
			setRs(dbc, employees);
			dbc.executeUpdate();
			System.out.println (sql);
			return true;
		}catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbc.close();
		}
	}
	
	public static boolean deleteEmployees(int id){
		DBConnect dbc = new DBConnect();
		String sql = "DELETE FROM info_employees WHERE id = " + id;
		dbc.prepareStatement(sql);
		try {
			dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally{
			dbc.close();
		}
	}
	
	public static boolean updateEmployees(Employees employees){
		DBConnect dbc = new DBConnect();
		String sql = "update info_employees set idcard=? , name=?, username=?,password=?, gender=?,birthday=?,nation=?, polity=?, " +
				"is_married=?,home_type=?, baby_birth=?, position=?, cellphone=?, phone=?,email=?,edu_level=?, family_address=?,area_id=?, " +
				"start_time=?,namepy=? where id='"+employees.getId()+"'";
		dbc.prepareStatement(sql);
		try {
			setRs(dbc, employees);
			dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally{
			dbc.close();
		}
	}
	
	public static boolean updatePassword(Employees employees){
		DBConnect dbc = new DBConnect();
		String sql ="update info_employees set password = ? where username = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setString(1, employees.getPassword());
			dbc.setString(2, employees.getUsername());
			System.out.println(sql);
			dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally{
			dbc.close();
		}
	}
	public static void setRs(DBConnect dbc,Employees employees){
		 int index=1;
		 try{
				dbc.setString(index++,employees.getIdcard());
				dbc.setString(index++, employees.getName());
				dbc.setString(index++, employees.getUsername());
				dbc.setString(index++, employees.getPassword());
				dbc.setString(index++, employees.getGender());
				dbc.setString(index++, employees.getBirthday());
				dbc.setInt(index++, employees.getNation());
				dbc.setInt(index++, employees.getPolity());
				dbc.setString(index++, employees.getIs_married());
				dbc.setString(index++, employees.getHome_type());
				dbc.setString(index++, employees.getBaby_birth());
				dbc.setInt(index++, employees.getPosition());
				dbc.setString(index++, employees.getCellphone());
				dbc.setString(index++, employees.getPhone());
				dbc.setString(index++, employees.getEmail());
				dbc.setString(index++, employees.getEdu_level());
				dbc.setString(index++, employees.getFamily_address());
				dbc.setInt(index++, employees.getArea_id());
				dbc.setString(index++, employees.getStart_time());
				dbc.setString(index++, employees.getNamepy());
		 }catch (Exception e) {
			 e.printStackTrace();
			// TODO: handle exception
			 System.out.println("setRs error!");
		}
	}
	/**
	 * @param employees
	 * @param rs
	 */
	public static void getByRs(Employees employees,ResultSet rs){
		try{
			employees.setId(rs.getInt("id"));
			employees.setIdcard(rs.getString("idcard"));
			employees.setName(rs.getString("name"));
			employees.setUsername(rs.getString("username"));
			employees.setPassword(rs.getString("password"));
			employees.setGender(rs.getString("gender"));
			employees.setBirthday(rs.getString("birthday"));
			employees.setNation(rs.getInt("nation"));
			employees.setPolity(rs.getInt("polity"));
			employees.setIs_married(rs.getString("is_married"));
			employees.setHome_type(rs.getString("home_type"));
			employees.setBaby_birth(rs.getString("baby_birth"));
			employees.setPosition(rs.getInt("position"));
			employees.setCellphone(rs.getString("cellphone"));
			employees.setPhone(rs.getString("phone"));
			employees.setEmail(rs.getString("email"));
			employees.setEdu_level(rs.getString("edu_level"));
			employees.setFamily_address(rs.getString("family_address"));
			employees.setArea_id(rs.getInt("area_id"));
			employees.setStart_time(rs.getString("start_time"));
			employees.setNamepy(rs.getString("namepy"));
		}catch (SQLException e) {
			e.printStackTrace();
			System.out.println("getByRs error!");
		}
	}
	
	/**
	 * 根据类型获（销售员。。。）取员工
	 */
	public static List<Employees> getEmployeesByPosition(int position) {
		List<Employees> es = new ArrayList<Employees>();
		DBConnect dbConnect = new DBConnect();
		String sql = "select * from info_employees where position = '"+position+"'";
		dbConnect.prepareStatement(sql);
		try {
			ResultSet rs = dbConnect.executeQuery();
			while(rs.next()){
				Employees e = new Employees();
				getByRs(e, rs);
				es.add(e);
			}
			return es;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			dbConnect.close();
		}
		return null;
	}
	
	 /**
	 * @param 根据以下字段搜索employee
	 * @param idcard
	 * @param username
	 * @param gender
	 * @param nation
	 * @param polity
	 * @param is_married
	 * @param home_type
	 * @param position
	 * @param edu_level
	 * @param area_id
	 * @param rowsPerpage
	 * @param pages
	 * @return
	 */
	public static Collection searchEmployees(String idcard,String name,String gender,String nation,String polity,
	String is_married,String home_type,String position,String edu_level,String area_id,int rowsPerpage,int pages){
		if(idcard==null||idcard.equals("")){
			idcard="%";
		}
		if(name==null||name.equals("")){
			name="%";
		}
		if(gender==null||gender.equals("")){
			gender="%";
		}
		if(nation==null||nation.equals("")){
			nation="%";
		}
		if(polity==null||polity.equals("")){
			polity="%";
		}
		if(is_married==null||is_married.equals("")){
			is_married="%";
		}
		if(home_type==null||home_type.equals("")){
			home_type="%";
		}
		if(position==null||position.equals("")){
			position="%";
		}
		if(edu_level==null||edu_level.equals("")){
			edu_level="%";
		}
		if(area_id==null||area_id.equals("")){
			area_id="%";
		}
		String sqlString = "select * from info_employees where idcard like '%"+idcard+"%' and name like '%"+name+"%' " +
				"and gender like '%"+gender+"%' and  nation like '%"+nation+"%' and  polity like '%"+polity+"%' and  is_married like '%"+is_married+"%' " +
						"and  home_type like '%"+home_type+"%' and  position like '%"+position+"%' and  edu_level like '%"+edu_level+"%' " +
								"and  area_id like '%"+area_id+"%'";
		System.out.println(sqlString);
		pageInstance = new EmployeesControlPage(sqlString, rowsPerpage);
		try{
			return pageInstance.getPage(pages);
		}catch (Exception e) {
			e.printStackTrace();
			return null;
		}
}
	
}

/**
 * @author zh
 * @return elist
 */
class EmployeesControlPage extends Pagination{
	public EmployeesControlPage(String sql,int rowsPerPage){
		super(sql ,rowsPerPage,1);
	}
	
	protected Collection<Employees> packResultSet(ResultSet rs) {
		Collection<Employees> elist=new ArrayList<Employees>();
		try{
			while(rs.next()){
				Employees employees = new Employees();
				EmployeesControl.getByRs(employees, rs);
				elist.add(employees);
				employees = null;
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		return elist;
	}
}
